package com.gisuni.sddc.service;

import java.util.List;
import java.util.Map;

import org.nutz.log.Log;
import org.nutz.log.Logs;
import org.json.JSONArray;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;

import com.gisuni.sddc.dao.AdvQueryDao;
import com.gisuni.sddc.dao.LandStatisticsDao;

@IocBean
public class AdvQueryService {

	private Log log = Logs.getLog(AdvQueryService.class);
	
	@Inject
	private AdvQueryDao advQueryDao;
	
	/**
	 * 包含土地类型和控规的高级查询
	 * @param map
	 * @return
	 */
	public List<Map<String,Object>> advQuery(Map<String, Object> map) {
		String landTypeSql=constructLandTypeSql(map);
		String KGSql=constructKGSql(map);
		String TGSql=constructTGSql(map);
		String landType=map.get("landType").toString();
		//土地类型+控规做交集SQL
		String insertSql = "";
		if(map.get("mainBody").equals("TDLX")){//土地类型为主体
			landTypeSql=mainBodySQLComplete(landTypeSql,map);
			insertSql += insertSqlMainBodyLandType(landType,landTypeSql,KGSql,TGSql);
		}/*else if(map.get("mainBody").equals("KG")){//控规为主体
			KGSql=mainBodySQLComplete(KGSql,map);
			insertSql += insertSqlMainBodyKG(landTypeSql,KGSql,TGSql);
		}else if(map.get("mainBody").equals("TG")){
			TGSql=mainBodySQLComplete(TGSql,map);
			insertSql += insertSqlMainBodyTG(landTypeSql,KGSql,TGSql);
		}*/
		List<Map<String,Object>> list=null;
		if(!"".equals(KGSql) && !"".equals(TGSql)){
			list= advQueryDao.advQuery(insertSql,true);
		}else{
			list= advQueryDao.advQuery(insertSql,false);
		}
		return list;
	}
	/**
	 * 构建 土地类型SQL
	 * @param map
	 * @return
	 */
	private String constructLandTypeSql(Map<String, Object> map){
		String landTypeSql="";
		if((Boolean) map.get("landTypeFlag")){
			//土地类型SQL
			landTypeSql +="select OBJECTID,SHAPE from ";
			landTypeSql +=map.get("landTypeTable") +" where fqmc = '"+map.get("county")+"'";
			String landType=map.get("landType").toString();
			if("纳入三旧的旧厂".equals(landType) || "纳入三旧的旧城".equals(landType)){
				landTypeSql+=" AND "+map.get("landTypeQueryField")+"='国有用地'";
			}else if("纳入三旧的集体旧厂".equals(landType) || "纳入三旧的旧村".equals(landType)){
				landTypeSql+=" AND "+map.get("landTypeQueryField")+"='集体用地'";
			}/*else{
				landTypeSql+=" AND "+map.get("landTypeQueryField")+"<>0";
			}*/
		}
		return landTypeSql;
	}
	/**
	 * 构建控规SQL
	 * @param map
	 * @return
	 */
	private String constructKGSql(Map<String, Object> map){
		String KGSql="";
		if((Boolean) map.get("KGFlag")){
			//控规SQL
			KGSql+= "select SHAPE from "+map.get("KGTable")+" where fqmc='"+map.get("county")+"' AND ";
			KGSql+= map.get("KGQueryField")+" IN (";
			JSONArray jsonArray = (JSONArray) map.get("KG");
			for(int i=0;i<jsonArray.length();i++){
				if(i==(jsonArray.length()-1)){
					KGSql+="'"+jsonArray.getString(i)+"')";
					//KGSql+=map.get("KGQueryField")+"='"+jsonArray.getString(i)+"'";
				}else{
					KGSql+="'"+jsonArray.getString(i)+"',";
					//KGSql+=map.get("KGQueryField")+"='"+jsonArray.getString(i)+"' or ";
				}
			}
		}
		return KGSql;
	}
	/**
	 * 构建土规SQL
	 * @param map
	 * @return
	 */
	private String constructTGSql(Map<String, Object> map){
		String TGSql="";
		if((Boolean) map.get("TGFlag")){
			//土规SQL
			TGSql +="select SHAPE from " + map.get("TGTable") +" where fqmc = '"+map.get("county")+"' AND "+map.get("TGQueryField")+"='"+map.get("TG")+"'";
		}
		return TGSql;
	}
	/**
	 * 完善主体SQL(主体可能是土地类型、可能是控规、也可能是土规)
	 * @param mainBodySql
	 * @param map
	 * @return
	 */
	private String mainBodySQLComplete(String mainBodySql,Map<String, Object> map){
		if(!"".equals(map.get("startValue"))){
			mainBodySql +=" and "+map.get("landTypeAreaField")+" >"+map.get("startValue").toString();
		}
		if(!"".equals(map.get("endValue"))){
			mainBodySql +=" and "+map.get("landTypeAreaField")+" <"+map.get("endValue").toString();
		}
		return mainBodySql;
	}
	/**
	 * 以土地类型为主体的 空间相交SQL构建
	 * @param landTypeSql
	 * @param KGSql
	 * @param TGSql
	 * @return
	 */
	private String insertSqlMainBodyLandType(String landType,String landTypeSql,String KGSql,String TGSql){
		String insertSql="";
		if(!"".equals(KGSql) && "".equals(TGSql)){
			insertSql +="select distinct(landType.OBJECTID) from ";
			insertSql+="("+landTypeSql+") landType,";
			insertSql+="("+KGSql+") kg1";
			insertSql+=" where SDE.ST_intersects(landType.shape,kg1.shape)=1";
		}else if(!"".equals(TGSql) && "".equals(KGSql)){
			insertSql +="select distinct(landType.OBJECTID) from ";
			insertSql+="("+landTypeSql+") landType,";
			insertSql+="("+TGSql+") tg1";
			insertSql+=" where SDE.ST_intersects(landType.shape,tg1.shape)=1";
		}else if(!"".equals(KGSql) && !"".equals(TGSql)){//三个条件的时间上需要优化
			insertSql+="select tmp.OBJECTID from (select distinct(landType.OBJECTID) OBJECTID,landType.shape shape from";
			insertSql+="("+landTypeSql+") landType,";
			insertSql+="("+KGSql+") kg1";
			insertSql+=" where SDE.ST_intersects(landType.shape,kg1.shape)=1) tmp,";
			insertSql+="("+TGSql+") tg1";
			insertSql+=" where  SDE.ST_intersects(tmp.shape,tg1.shape)=1";
		}
		System.out.println(insertSql);
		return insertSql;
	}
	/**
	 * 以控规为主体的 空间相交SQL构建
	 * @param landTypeSql
	 * @param KGSql
	 * @param TGSql
	 * @return
	 */
	/*private String insertSqlMainBodyKG(String landTypeSql,String KGSql,String TGSql){
		String insertSql="select distinct kg1.objectid,kg1.Csmc,kg1.fqmc,kg1.KGFLZL,kg1.YDMJ from ";
		insertSql+="("+KGSql+") kg1,";
		if(!"".equals(landTypeSql) && "".equals(TGSql)){
			insertSql+="("+landTypeSql+") landType";
			insertSql+=" where SDE.ST_intersects(landType.shape,kg1.shape)=1";
		}else if(!"".equals(TGSql) && "".equals(landTypeSql)){
			insertSql+="("+TGSql+") tg1";
			insertSql+=" where SDE.ST_intersects(tg1.shape,kg1.shape)=1";
		}else if(!"".equals(TGSql) && !"".equals(landTypeSql)){//三个条件时间上需要优化
			insertSql+="("+landTypeSql+") landType,";
			insertSql+="("+TGSql+") tg1";
			insertSql+=" where SDE.ST_intersects(landType.shape,kg1.shape)=1 and SDE.ST_intersects(kg1.shape,tg1.shape)=1";
		}
		return insertSql;
	}*/
	
	/**
	 * 以土规为主体的 空间相交SQL构建
	 * @param landTypeSql
	 * @param KGSql
	 * @param TGSql
	 * @return
	 */
	/*private String insertSqlMainBodyTG(String landTypeSql,String KGSql,String TGSql){
		String insertSql="select distinct tg1.OBJECTID,tg1.CSMC,tg1.FQMC,tg1.TGFLDL,tg1.YDMJ from ";
		insertSql+="("+TGSql+") tg1,";
		if(!"".equals(landTypeSql) && "".equals(KGSql)){
			insertSql+="("+landTypeSql+") landType";
			insertSql+=" where SDE.ST_intersects(landType.shape,tg1.shape)=1";
		}else if(!"".equals(KGSql) && "".equals(landTypeSql)){
			insertSql+="("+KGSql+") kg1";
			insertSql+=" where SDE.ST_intersects(tg1.shape,kg1.shape)=1";
		}else if(!"".equals(KGSql) && !"".equals(landTypeSql)){//三个条件时间比较长
			insertSql+="("+landTypeSql+") landType,";
			insertSql+="("+KGSql+") kg1";
			insertSql+=" where SDE.ST_intersects(kg1.shape,tg1.shape)=1 and SDE.ST_intersects(landType.shape,tg1.shape)=1";
		}
		return insertSql;
	}*/
}
